package com.situ.stl.dao;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface ChartsMapper {
    /**
     * 统计每天课程的数量
     * @param date
     * @return
     */
    @Select({
            "select date(time) day, count(0) count from course",
            "where time>#{date}",
            "group by date(time)",
            "order by date(time)",
    })
    List<Map<String, Object>> selectCourseCount(Date date);

    /**
     * 统计每天预约数量
     * @param date
     * @return
     */
    @Select({
            "select date(time) day, count(0) count from reserve",
            "left join course on reserve.course_id=course.id",
            "where time>#{date}",
            "group by date(time)",
            "order by date(time)",
    })
    List<Map<String, Object>> selectReserveCount(Date date);

    /**
     * 统计每天带课教练的数量
     * @param date
     * @return
     */
    @Select({
            "select date(time) day, count(0) count from ",
            "(  select * from course",
            "   where time>#{date}",
            "   group by date(time), coach_id) as c",
            "group by date(time)",
            "order by date(time)",
    })
    List<Map<String, Object>> selectCoachCount(Date date);

    /**
     * 统计每天会员的数量
     * @param date
     * @return
     */
    @Select({
            "select date(create_time) day, count(0) count from member",
            "where create_time>#{date}",
            "group by date(create_time)",
            "order by date(create_time)",
    })
    List<Map<String, Object>> selectMemberCount(Date date);

    /**
     * 最近最热的课程
     * @param date
     * @return
     */
    @Select({
            "select course.name, count(0) count from course",
            "left join reserve on course.id = reserve.course_id",
            "where time > #{date}",
            "group by course.id",
            "order by count(0) desc",
            "limit 10",
    })
    List<Map<String, Object>> selectCourseOrderByReserve(Date date);

    /**
     * 最近最热门的教练
     * @param date
     * @return
     */
    @Select({
            "select coach.name, avg(reserve.score) count from coach",
            "left join course on course.coach_id = coach.id",
            "left join reserve on reserve.course_id = course.id",
            "where course.time > #{date}",
            "group by coach.id",
            "order by avg(reserve.score) desc",
            "limit 5",
    })
    List<Map<String, Object>> selectCoachOrderByReserve(Date date);

    /**
     * 统计所有会员数量
     * @return
     */
    @Select({
            "select count(0) count from member",
    })
    Map<String, Object> selectMemberTotal();

    /**
     * 统计所有教练数量
     * @return
     */
    @Select({
            "select count(0) count from coach",
    })
    Map<String, Object> selectCocahTotal();

    /**
     * 统计所有课程数量
     * @return
     */
    @Select({
            "select count(0) count from course",
    })
    Map<String, Object> selectCourseTotal();

    /**
     * 统计所有预约数量
     * @return
     */
    @Select({
            "select count(0) count from reserve",
    })
    Map<String, Object> selectReserveTotal();

}
